﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
namespace Client.SqlServer
{
    public class DbContext
    {
        //创建 ExeConfigurationFileMap 对象并指定配置文件路径
        System.Configuration.Configuration config = System.Configuration.ConfigurationManager.OpenMappedExeConfiguration(new System.Configuration.ExeConfigurationFileMap()
        {
            ExeConfigFilename = AppDomain.CurrentDomain.BaseDirectory + @"Configuration\App.config"
        }, System.Configuration.ConfigurationUserLevel.None);
        /// <summary>
        /// 
        /// </summary>
        private SqlConnection connection_;

        private string SqlConfigstr_;
         /// <summary>
         /// 连接字符串
         /// </summary>
        public string SqlConfigstr
        {
            get
            {
                if (string.IsNullOrEmpty(SqlConfigstr_))
                {
                    string connectionString = config.AppSettings.Settings["SQL"].Value;
                    return connectionString;
                }
                else
                {

                    return SqlConfigstr_;
                }
            }
            set
            {
                if (connection_ != null && SqlConfigstr != connection_.ConnectionString)
                {
                    connection_ = new SqlConnection(SqlConfigstr);
                }
                SqlConfigstr_ = value;
            }
        }

        public SqlConnection connection
        {
            get
            {
                if (connection_ != null && SqlConfigstr != connection_.ConnectionString)
                {
                    connection_ = new SqlConnection(SqlConfigstr);
                    return connection_;
                }
                if (connection_ == null)
                {
                    connection_ = new SqlConnection(SqlConfigstr);
                    return connection_;
                }
                else
                {
                    return connection_;
                }


            }
        }
        /// <summary>
        /// 查询数据表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable Where(string sql)
        {
                using (SqlDataAdapter cmd = new SqlDataAdapter(sql, connection))
                {
                    DataTable dt = new DataTable();
                    cmd.Fill(dt);
                    connection.Close();
                    return dt;
                }
           
        }
        /// <summary>
        /// 返回数据数量
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int Count(string sql)
        {

            try
            {
                using (SqlDataAdapter cmd = new SqlDataAdapter(sql, connection))
                {
                    DataTable dt = new DataTable();
                    cmd.Fill(dt);
                    connection.Close();
                    return dt.AsEnumerable().AsQueryable().Select(o=>new
                    {
                        count=o.Field<int>("count")
                    }).ToList().FirstOrDefault().count;
                }
            }
            catch
            {

            }
            return 0;
        }
        /// <summary>
        /// 查询数据表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable Where(string sql, out string excep)
        {
            excep = "";
            try
            {
                using (SqlDataAdapter cmd = new SqlDataAdapter(sql, connection))
                {
                    DataTable dt = new DataTable();
                    cmd.Fill(dt);
                    connection.Close();
                    return dt;
                }
            }
            catch (Exception ex)
            {
                excep = ex.Message;
            }
            return null;
        }
        /// <summary>
        /// 查询返回实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public List<T> Where<T>(string sql) where T : class, new()
        {
            using (SqlDataAdapter cmd = new SqlDataAdapter(sql, connection))
            {
                DataTable dt = new DataTable();
                cmd.Fill(dt);
                connection.Close();
                return ListData<T>(dt);
            }

        }
        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int Update(string sql)
        {
            try
            {
                SqlCommand sqlCommand = new SqlCommand();
                sqlCommand.Connection = connection;
                sqlCommand.CommandText = sql;
                if (connection.State == ConnectionState.Closed) connection.Open();
                return sqlCommand.ExecuteNonQuery();

            }
            catch
            {

            }
            finally
            {
                connection.Close();
            }

            return 0;
        }
        public int UpdateBegin(List<string> sql)
        {
            if (sql.Any() == false) return 0;
            if (connection.State == ConnectionState.Closed) connection.Open();
            SqlTransaction transaction = connection.BeginTransaction();//开启事务
            try
            {
                SqlCommand command = connection.CreateCommand();//创建命令
                command.Transaction = transaction;

                foreach (var item in sql)
                {
                    command.CommandText = item;
                    command.ExecuteNonQuery();
                }
                // 提交事务
                transaction.Commit();
                return 1;

            }
            catch
            {
                //回滚事务
                transaction.Rollback();
            }
            finally
            {
                connection.Close();
            }

            return 0;
        }
        /// <summary>
        /// DataTable转List
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dataTable"></param>
        /// <returns></returns>
        private List<T> ListData<T>(DataTable dataTable) where T : class, new()
        {
            var list = new List<T>();
            var ps = typeof(T).GetProperties();

            //遍历所有DataTable的行
            foreach (DataRow dr in dataTable.Rows)
            {
                var t = new T();
                //通过反射获取T类型的所有成员
                foreach (System.Reflection.PropertyInfo pi in ps)
                {
                    if (dataTable.Columns.Contains(pi.Name) && dr[pi.Name] != DBNull.Value)
                    {

                        object value = Convert.ChangeType(dr[pi.Name], pi.PropertyType, null);
                        pi.SetValue(t, value, null);
                    }
                }
                //将T类型添加到集合list
                list.Add(t);
            }
            return list;
        }
        /// <summary>
        /// DataTable转List
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dataTable"></param>
        /// <returns></returns>
        private List<T> ListData<T>(DataTable dataTable, out string exception) where T : class, new()
        {
            var list = new List<T>();
            var ps = typeof(T).GetProperties();
            exception = null;
            //遍历所有DataTable的行
            try
            {
                foreach (DataRow dr in dataTable.Rows)
                {
                    var t = new T();
                    //通过反射获取T类型的所有成员
                    foreach (System.Reflection.PropertyInfo pi in ps)
                    {
                        if (dataTable.Columns.Contains(pi.Name) && dr[pi.Name] != DBNull.Value)
                        {
                            object value = Convert.ChangeType(dr[pi.Name], pi.PropertyType, null);
                            //给T类型字段赋值
                            pi.SetValue(t, value, null);
                        }
                    }
                    //将T类型添加到集合list
                    list.Add(t);
                }
            }
            catch (Exception ex)
            {
                exception = ex.Message;
            }
            return list;
        }
    }
}
